/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.CTHoaDon;
import POJO.NhanVien;
import POJO.SanPhamBanTrongNgay;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hồng
 */
public class CTHoaDonDao {

	public static ArrayList<CTHoaDon> layDanhSach() {
		ArrayList<CTHoaDon> ds = new ArrayList<CTHoaDon>();
		String sql = "SELECT  MaCTHD, SoLuong, GiaBan, MaHD, MaSP   FROM  CTHoaDon ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCTHD");
				int soluong = rs.getInt("SoLuong");
				Float gia = rs.getFloat("GiaBan");
				int mahd = rs.getInt("MaHD");
				int masp = rs.getInt("MaSP");
				CTHoaDon tmp = new CTHoaDon(ma, soluong, gia, mahd, masp);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<CTHoaDon> timKiemByHoaDon(int maHD) {
		ArrayList<CTHoaDon> ds = new ArrayList<CTHoaDon>();

		String sql = "SELECT * FROM CTHoaDon WHERE MaHD LIKE '%" + maHD + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCTHD");
				int soluong = rs.getInt("SoLuong");
				Float gia = rs.getFloat("GiaBan");
				int mahd = rs.getInt("MaHD");
				int masp = rs.getInt("MaSP");
				CTHoaDon tmp = new CTHoaDon(ma, soluong, gia, mahd, masp);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<SanPhamBanTrongNgay> layDanhSachSanPhamDaBan(int manv) {
		ArrayList<SanPhamBanTrongNgay> ds = new ArrayList<SanPhamBanTrongNgay>();
		String str = "SELECT sp.TenSP, cthd.SoLuong, cthd.GiaBan FROM SANPHAM sp, HOADON hd, ";
		str += "CTHOADON cthd where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
		str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,hd.NgayLap)=DATEPART(MM,GETDATE()) AND Datepart(DD,hd.NgayLap)=DATEPART(DD,GETDATE())";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPhamBanTrongNgay tmp = new SanPhamBanTrongNgay();
				tmp.setTenSP(rs.getString("TenSP"));
				tmp.setSl(rs.getInt("SoLuong"));
				tmp.setGia(rs.getDouble("GiaBan"));
				tmp.setThanhTien(rs.getInt("SoLuong") * rs.getDouble("GiaBan"));
				ds.add(tmp);
			}
//		} catch (Exception ex) {
//			System.out.println(ex.getMessage());
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
			//	System.out.println(ex.getMessage());

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<SanPhamBanTrongNgay> layDanhSachMatHangDaBanTheoThang(int manv, int thang, int nam) {
		ArrayList<SanPhamBanTrongNgay> ds = new ArrayList<SanPhamBanTrongNgay>();
		String str = "SELECT sp.TenSP, cthd.SoLuong, cthd.GiaBan FROM SANPHAM sp, HOADON hd, ";
		str += "CTHOADON cthd where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
		str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		str += " and Datepart(mm,hd.NgayLap)='" + thang + "'";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPhamBanTrongNgay tmp = new SanPhamBanTrongNgay();
				tmp.setTenSP(rs.getString("TenSP"));
				tmp.setSl(rs.getInt("SoLuong"));
				tmp.setGia(rs.getDouble("GiaBan"));
				tmp.setThanhTien(rs.getInt("SoLuong") * rs.getDouble("GiaBan"));
				ds.add(tmp);
			}
//		} catch (Exception ex) {
//			System.out.println(ex.getMessage());
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
			//	System.out.println(ex.getMessage());

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<SanPhamBanTrongNgay> layDanhSachMatHangDaBanTheoNam(int manv, int nam) {
		ArrayList<SanPhamBanTrongNgay> ds = new ArrayList<SanPhamBanTrongNgay>();
		String str = "";
		if (manv == 0) {// load tat ca
			str = "SELECT sp.TenSP, cthd.SoLuong, cthd.GiaBan FROM SANPHAM sp, HOADON hd, ";
			str += "CTHOADON cthd where cthd.MaHD= hd.MaHD ";
			str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		} else {// load theo nhan vien
			str = "SELECT sp.TenSP, cthd.SoLuong, cthd.GiaBan FROM SANPHAM sp, HOADON hd, ";
			str += "CTHOADON cthd where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
			str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		}
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPhamBanTrongNgay tmp = new SanPhamBanTrongNgay();
				tmp.setTenSP(rs.getString("TenSP"));
				tmp.setSl(rs.getInt("SoLuong"));
				tmp.setGia(rs.getDouble("GiaBan"));
				tmp.setThanhTien(rs.getInt("SoLuong") * rs.getDouble("GiaBan"));
				ds.add(tmp);
			}
//		} catch (Exception ex) {
//			System.out.println(ex.getMessage());
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
			//	System.out.println(ex.getMessage());

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int TongSanPhamDaBanTrongNgay(int manv) {
		int tong = 0;
		String str = "Select SUM(cthd.SoLuong) as Tong FROM SANPHAM sp, HOADON hd, CTHOADON cthd ";
		str += "where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
		str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,hd.NgayLap)=DATEPART(MM,GETDATE()) AND Datepart(DD,hd.NgayLap)=DATEPART(DD,GETDATE())";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static int TongSanPhamDaBanTrongThang(int manv, int thang, int nam) {
		int tong = 0;
		String str = "Select SUM(cthd.SoLuong) as Tong FROM SANPHAM sp, HOADON hd, CTHOADON cthd ";
		str += "where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
		str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		str += " and Datepart(mm,hd.NgayLap)='" + thang + "'";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}

		return tong;

	}

	public static int TongSanPhamDaBanTrongNam(int manv, int nam) {
		int tong = 0;
		String str = "Select SUM(cthd.SoLuong) as Tong FROM SANPHAM sp, HOADON hd, CTHOADON cthd ";
		str += "where hd.MaNVPhuTrach = '" + manv + "' and cthd.MaHD= hd.MaHD ";
		str += " and sp.MaSP=cthd.MaSP and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaBanTrongNgay(int manv) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia - hd.SoTienGiam) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(yyyy,hd.NgayLap)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,hd.NgayLap)=DATEPART(MM,GETDATE()) AND Datepart(DD,hd.NgayLap)=DATEPART(DD,GETDATE())";

		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaBanTrongThang(int manv, int thang, int nam) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia - hd.SoTienGiam) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(yyyy,hd.NgayLap)='" + nam + "'";
		str += " and Datepart(mm,hd.NgayLap)='" + thang + "'";

		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaBanTrongNam(int manv, int nam) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia - hd.SoTienGiam) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(yyyy,hd.NgayLap)='" + nam + "'";

		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaThuTrongNgay(int manv) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(yyyy,hd.NgayLap)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,hd.NgayLap)=DATEPART(MM,GETDATE()) AND Datepart(DD,hd.NgayLap)=DATEPART(DD,GETDATE())";
		str += " and hd.TrangThai='2'";
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaThuTrongThang(int manv, int thang, int nam) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(mm,hd.NgayLap)='" + thang + "' AND Datepart(YYYY,hd.NgayLap)='" + nam + "'";
		str += " and hd.TrangThai='2'";
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongTienDaThuTrongNam(int manv, int nam) {
		int tong = 0;
		String str = "Select SUM(hd.TriGia) as Tong FROM HOADON hd where hd.MaNVPhuTrach = '" + manv + "'";
		str += " and Datepart(YYYY,hd.NgayLap)='" + nam + "'";
		str += " and hd.TrangThai='2'";
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static int layMaCTHDMax() {
		String sql = "select ct.MaCTHD from CTHoaDon ct where ct.MaCTHD >= all(select ct1.MaCTHD from CTHoaDon ct1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("MaCTHD");
			}
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static int laySoLuong(int MaHD) {
		String sql = "select SoLuong from CTHoaDon where MaHD = " + MaHD;
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("SoLuong");
			}
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean them(ArrayList<CTHoaDon> tmp) {
		boolean kq = false;

		String sql = "";
		int maCTHDMax = CTHoaDonDao.layMaCTHDMax();
		maCTHDMax = maCTHDMax + 1;
		for (int i = 0; i < tmp.size(); i++) {
			tmp.get(i).setMaCTHD(maCTHDMax + i);
			String s = "INSERT INTO  CTHoaDon  (MaCTHD,SoLuong,GiaBan,MaHD,MaSP) VALUES ";
			s += String.format("( %d, %d, %f, %d, %d);",
				 tmp.get(i).getMaCTHD(),
				 tmp.get(i).getSoLuong(),
				 tmp.get(i).getGiaBan(),
				 tmp.get(i).getMaHD(),
				 tmp.get(i).getMaSP());

			sql = sql + s;
		}
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		if (n >= 1) {
			kq = true;
		}
		return kq;
	}

	public static boolean them(CTHoaDon cthd) {
		boolean kq = false;
		String sql = "INSERT INTO  CTHoaDon  ( MaCTHD, SoLuong, GiaBan, MaHD, MaSP  ) VALUES ";

		String s = String.format("( %d, %d, %f, %d, %d)", cthd.getMaCTHD(), cthd.getSoLuong(), cthd.getGiaBan(), cthd.getMaHD(), cthd.getMaSP());
		s += sql;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean xoa(int ma) {
		boolean kq = false;
		String sql = String.format("DELETE FROM CTHoaDon WHERE MaCTHD=%d", ma);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean capNhat(CTHoaDon tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE CTHoaDon"
			 + "SET"
			 + "SoLuong = %d, "
			 + "GiaBan = %f,"
			 + "MaHD = %d,"
			 + "MaSP = %d"
			 + "WHERE  MaCTHD = %d; ", tmp.getSoLuong(), tmp.getGiaBan(), tmp.getMaHD(), tmp.getMaSP(), tmp.getMaCTHD());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static ArrayList<CTHoaDon> layByMa(int maHD) {
		ArrayList<CTHoaDon> ds = new ArrayList<CTHoaDon>();

		CTHoaDon tmp = null;
		try {
			String sql = "SELECT * FROM CTHoaDon WHERE MaCTHD =" + maHD;
			KetNoi helper = new KetNoi();
			ResultSet rs = helper.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaCTHD");
				int soluong = rs.getInt("SoLuong");
				Float gia = rs.getFloat("GiaBan");
				int mahd = rs.getInt("MaHD");
				int masp = rs.getInt("MaSP");
				tmp = new CTHoaDon(ma, soluong, gia, mahd, masp);
				ds.add(tmp);
			}
			helper.dongKetNoi();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return ds;
	}

	public static ArrayList<CTHoaDon> layDSByMaHD(int mahd) {
		ArrayList<CTHoaDon> ds = new ArrayList<CTHoaDon>();
		String sql = "select * from cthoadon where mahd = " + mahd;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCTHD");
				int soluong = rs.getInt("SoLuong");
				Float gia = rs.getFloat("GiaBan");

				int masp = rs.getInt("MaSP");
				CTHoaDon tmp = new CTHoaDon(ma, soluong, gia, mahd, masp);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTHoaDonDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
}
